﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Justin.BI.TestDataTools.DAL;
using Justin.BI.TestDataTools.Models;
using Justin.BI.TestDataTools.Settings;
using Justin.BI.TestDataTools.Utility;

namespace Justin.BI.TestDataTools.Models
{
    public delegate void SQLProcess(StringBuilder sqlBuilder,JTable table);
    public class JTable
    {
        public JTable(DBTable dbTable)
        {
            this.TableName = dbTable.TableName;
            this.Fields = new List<JField>();
            foreach (var item in dbTable.Columns)
            {
                JField field = new JField(item);
                this.Fields.Add(field);
            }
            if (dbTable.PrimaryKey != null)
                this.Fields.Add(new JField(dbTable.PrimaryKey));

            foreach (var fk in dbTable.ForeignKeys)
            {
                this.Fields.Add(new JField(fk));
            }
        }
        public string TableName { get; set; }
        public int DataCount { get; set; }
        public string BeforeSQL { get; set; }
        public string AfterSQL { get; set; }
        public List<JField> Fields { get; set; }
        public int Order = 1000;
        public JTable() { }

        public static SQLProcess SqlProcess { get; set; }

        public void Process()
        {
            JTable table = this;


            Random rd = new Random();
            StringBuilder builder = new StringBuilder();
            if (!string.IsNullOrEmpty(table.BeforeSQL))
            {
                builder.Append(table.BeforeSQL).AppendLine();
            }
            for (int i = 0; i < table.DataCount; i++)
            {
                // List<SourceFieldData> fieldDatas = new List<SourceFieldData>();

                #region 生成每一条数据
                string format = "insert into {0}({1}) values({2});";
                StringBuilder fieldNameBuilder = new StringBuilder();
                StringBuilder fieldValueBuilder = new StringBuilder();
                Dictionary<string, object> dataRow = new Dictionary<string, object>();

                JField[] fields = table.Fields.Where(row => row.Visible == true).OrderBy(row => row.Order).ToArray();
                for (int f = 0; f < fields.Count(); f++)
                {
                    JField field = fields[f];
                    try
                    {

                        fieldNameBuilder.AppendFormat("{0},", field.FieldName);
                        object value = "";

                        #region 生成Value

                        switch (field.SourceInfo.SourceValueCategroy)
                        {
                            case JSourceValueCategroy.List:

                                #region List不管是DateTime、Numeric、String处理方式都一样

                                object[] values = field.SourceInfo.Sources.ToArray();
                                int listCount = field.SourceInfo.Sources.Count();
                                int randNum = rd.Next(0, listCount);
                                value = values[randNum];
                                break;

                                #endregion

                            case JSourceValueCategroy.Range:

                                #region Range

                                switch (field.ValueType)
                                {
                                    case JValueType.DateTime:

                                        #region Range

                                        DateTime minDate = DateTime.Parse(field.SourceInfo.MinValue.ToJString());
                                        DateTime maxDate = DateTime.Parse(field.SourceInfo.MaxValue.ToJString());
                                        int minutesDiff = (int)(maxDate - minDate).TotalMinutes;
                                        int randMinutes = rd.Next(0, minutesDiff);
                                        value = minDate.AddMinutes(randMinutes);

                                        break;

                                        #endregion

                                    case JValueType.Numeric:

                                        #region Range

                                        decimal maxSeed = decimal.Parse(field.SourceInfo.MaxValue.ToJString());
                                        decimal minSeed = decimal.Parse(field.SourceInfo.MinValue.ToJString());
                                        if (maxSeed <= 1)
                                        {
                                            maxSeed = maxSeed * 10000;
                                            minSeed = minSeed * 10000;
                                            value = (decimal)rd.Next((int)minSeed, (int)maxSeed) / 10000;
                                        }
                                        else
                                        {
                                            value = rd.Next((int)minSeed, (int)maxSeed);
                                        }

                                        break;

                                        #endregion

                                    case JValueType.String:

                                        value = String.Format(field.Format, rd.Next((Int32)field.SourceInfo.MinValue, (Int32)field.SourceInfo.MaxValue));

                                        break;

                                }
                                break;

                                #endregion

                            case JSourceValueCategroy.Sequence:

                                #region Sequence

                                switch (field.ValueType)
                                {
                                    case JValueType.DateTime:
                                        #region Sequence

                                        throw GetException(field.FieldName, field.ValueType, field.SourceInfo.SourceValueCategroy);

                                        #endregion

                                    case JValueType.Numeric:
                                        #region Sequence

                                        value = field.SourceInfo.MinValue;
                                        field.SourceInfo.MinValue = int.Parse(field.SourceInfo.MinValue.ToJString("0")) + int.Parse(field.SourceInfo.Step.ToJString("0"));
                                        break;

                                        #endregion

                                    case JValueType.String:

                                        value = String.Format(field.Format, field.SourceInfo.MinValue);
                                        field.SourceInfo.MinValue = int.Parse(field.SourceInfo.MinValue.ToJString("0")) + int.Parse(field.SourceInfo.Step.ToJString("0"));
                                        break;

                                }
                                break;

                                #endregion

                            case JSourceValueCategroy.FromTable:

                                #region FromTable

                                string refTableName = field.SourceInfo.ReferenceTableName;
                                string refColumnName = field.SourceInfo.ReferenceColumnName;
                                List<string> parameters = new List<string>();
                                string filterFormat = field.SourceInfo.RefFilter.ToParameters(parameters);
                                List<object> parameterValues = new List<object>();
                                foreach (var item in parameters)
                                {
                                    parameterValues.Add(dataRow[item]);
                                }
                                string refFilter = string.Format(filterFormat, parameterValues.ToArray());


                                SourceFieldData tempData =
                                    tempData = new SourceFieldData()
                                    {
                                        fieldName = refColumnName,
                                        TableName = refTableName,
                                        Values = CommonDAL.GetValues(refTableName, refColumnName, refFilter).ToArray()
                                    };

                                value = tempData.Values[rd.Next(0, tempData.Values.Count())];

                                if (field.SourceInfo.FromTableCalculateValueUseOtherField)
                                {

                                    switch (field.ValueType)
                                    {
                                        case JValueType.DateTime:

                                            DateTime dtValue = DateTime.Parse(value.ToJString(DateTime.Now.ToString()));
                                            decimal value2 = field.SourceInfo.OtherFiledCalculateValue;
                                            #region 随机操作数

                                            if (field.SourceInfo.RandOtherFiledCalculateValue)
                                            {
                                                decimal otherMaxSeed = decimal.Parse(field.SourceInfo.MaxValue.ToJString());
                                                decimal otherMinSeed = decimal.Parse(field.SourceInfo.MinValue.ToJString());
                                                if (otherMaxSeed <= 1)
                                                {
                                                    otherMaxSeed = otherMaxSeed * 10000;
                                                    otherMinSeed = otherMinSeed * 10000;
                                                    value2 = (decimal)rd.Next((int)otherMinSeed, (int)otherMaxSeed) / 10000;
                                                }
                                                else
                                                {
                                                    value2 = rd.Next((int)otherMinSeed, (int)otherMaxSeed);
                                                }
                                            }

                                            #endregion

                                            value = dtValue.AddDays((double)value2);

                                            break;
                                    }
                                }
                                break;

                                #endregion

                            case JSourceValueCategroy.OtherField:

                                #region OtherField 数据准备
                                JField otherField = table.Fields.Where(row => row.FieldName == field.SourceInfo.OtherFiledName).FirstOrDefault();
                                if (otherField == null)
                                {
                                    throw new Exception("");
                                }
                                if (!dataRow.ContainsKey(field.SourceInfo.OtherFiledName))
                                {
                                    throw new Exception("");
                                }
                                object tempOtherFieldValue = dataRow[field.SourceInfo.OtherFiledName];

                                #endregion

                                #region 计算

                                if (field.SourceInfo.OtherFiledCalculateValue != 0 || (field.SourceInfo.OtherFiledCalculateValue == 0 && field.SourceInfo.RandOtherFiledCalculateValue))
                                {
                                    switch (field.ValueType)
                                    {
                                        case JValueType.DateTime:
                                            decimal dtvalue2 = field.SourceInfo.OtherFiledCalculateValue;
                                            #region 随机操作数

                                            if (field.SourceInfo.RandOtherFiledCalculateValue)
                                            {
                                                decimal otherMaxSeed = decimal.Parse(field.SourceInfo.MaxValue.ToJString());
                                                decimal otherMinSeed = decimal.Parse(field.SourceInfo.MinValue.ToJString());
                                                if (otherMaxSeed <= 1)
                                                {
                                                    otherMaxSeed = otherMaxSeed * 10000;
                                                    otherMinSeed = otherMinSeed * 10000;
                                                    dtvalue2 = (decimal)rd.Next((int)otherMinSeed, (int)otherMaxSeed) / 10000;
                                                }
                                                else
                                                {
                                                    dtvalue2 = rd.Next((int)otherMinSeed, (int)otherMaxSeed);
                                                }
                                            }

                                            #endregion



                                            switch (field.SourceInfo.OtherFiledCalculate)
                                            {
                                                case "+":
                                                    //dtvalue2 = dtvalue2; 
                                                    break;
                                                case "-": 
                                                    dtvalue2 = -dtvalue2;
                                                    break;
                                                case "*": 
                                                    //dtvalue2 = dtvalue2;
                                                    break;
                                                case "/": 
                                                    //dtvalue2 = dtvalue2; 
                                                    break;
                                            }
                                            value = DateTime.Parse(tempOtherFieldValue.ToJString(DateTime.Now.ToString())).AddDays((double)dtvalue2);
                                            break;
                                        case JValueType.Numeric:
                                            if (otherField.ValueType == JValueType.Numeric)
                                            {
                                                decimal value1 = decimal.Parse(tempOtherFieldValue.ToJString(DateTime.Now.ToString()));
                                                decimal value2 = field.SourceInfo.OtherFiledCalculateValue;
                                                #region 随机操作数

                                                if (field.SourceInfo.RandOtherFiledCalculateValue)
                                                {
                                                    decimal otherMaxSeed = decimal.Parse(field.SourceInfo.MaxValue.ToJString());
                                                    decimal otherMinSeed = decimal.Parse(field.SourceInfo.MinValue.ToJString());
                                                    if (otherMaxSeed <= 1)
                                                    {
                                                        otherMaxSeed = otherMaxSeed * 10000;
                                                        otherMinSeed = otherMinSeed * 10000;
                                                        value2 = (decimal)rd.Next((int)otherMinSeed, (int)otherMaxSeed) / 10000;
                                                    }
                                                    else
                                                    {
                                                        value2 = rd.Next((int)otherMinSeed, (int)otherMaxSeed);
                                                    }
                                                }

                                                #endregion

                                                switch (field.SourceInfo.OtherFiledCalculate)
                                                {
                                                    case "+": value = value1 + value2; break;
                                                    case "-": value = value1 - value2; break;
                                                    case "*": value = value1 * value2; break;
                                                    case "/": value = value1 / value2; break;
                                                }
                                            }
                                            break;
                                        case JValueType.String:

                                            break;
                                    }
                                }
                                else
                                {
                                    value = field.SourceInfo.OtherFiledCalculateValue;
                                }

                                break;

                                #endregion

                        }


                        #endregion

                        string numericFormat = "{0},";
                        string otherFormat = "'{0}',";
                        fieldValueBuilder.AppendFormat(
                            field.ValueType == JValueType.Numeric ? numericFormat : otherFormat
                            , value
                            );
                        dataRow.Add(field.FieldName, value);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(string.Format("Data：{0}-{1},Filed:{2} ErrorMessage:{3} ", field.ValueType.ToString(), field.SourceInfo.SourceValueCategroy.ToString(), field.FieldName, ex.ToString()));
                    }
                }
                #endregion

                builder.AppendFormat(format,
                    table.TableName,
                    fieldNameBuilder.ToString(0, fieldNameBuilder.Length - 1),
                    fieldValueBuilder.ToString(0, fieldValueBuilder.Length - 1)).AppendLine();

                if (builder.Length > Constants.SqlBufferSize && SqlProcess !=null)
                {
                    SqlProcess(builder,this);
                    //File.AppendAllText(fileName, builder.ToString());
                    builder.Clear();
                }

            }
            if (!string.IsNullOrEmpty(table.AfterSQL))
            {
                builder.Append(table.AfterSQL).AppendLine();
            }
            if (builder.Length > 0)
            {
                SqlProcess(builder,this);
                //File.AppendAllText(fileName, builder.ToString());
                builder.Clear();
            }
        }
        private NotSupportedException GetException(string filedName, JValueType valueType, JSourceValueCategroy sourceValueCategroy)
        {
            throw new NotSupportedException(string.Format("{0}不支持{1},Filed:{2}", valueType.ToString(), sourceValueCategroy.ToString(), filedName));
        }

    }

    public class JField
    {
        public JField()
        {
            this.Visible = false;
            this.Order = 0;
        }
        public JField(DBColumn column)
        {
            this.FieldName = column.ColumnName;
            this.ValueType = JField.GetJValueType(column.DbType);
            this.SourceInfo = new JSource();
            this.AllowNull = column.AllowNull;
            if (column is DBPrimaryKey)
            {
                DBPrimaryKey pk = column as DBPrimaryKey;
                this.SourceInfo.SourceValueCategroy = JSourceValueCategroy.Sequence;
                this.SourceInfo.MinValue = pk.CurrentValue + pk.Step;
                this.SourceInfo.Step = pk.Step;
            }
            if (column is DBForeignKey)
            {
                DBForeignKey fk = column as DBForeignKey;
                this.SourceInfo.SourceValueCategroy = JSourceValueCategroy.FromTable;
                this.SourceInfo.ReferenceTableName = fk.ReferenceTableName;
                this.SourceInfo.ReferenceColumnName = fk.ReferenceColumnName;
            }
            this.Visible = false;
            this.Order = 0;

        }
        public string FieldName { get; set; }
        public JValueType ValueType { get; set; }
        public JSource SourceInfo { get; set; }

        public bool Visible { get; set; }
        public string Format { get; set; }
        public void SetVisible(bool value)
        {
            if (AllowNull)
            {
                Visible = value;
            }
            else if (!AllowNull && !value)
            {
                throw new Exception(string.Format("字段{0}必须生成数据", FieldName));
            }
            else
            {
                Visible = true;
            }
        }
        public bool AllowNull { get; set; }
        public static JValueType GetJValueType(SqlDbType dbType)
        {

            switch (dbType)
            {
                case SqlDbType.BigInt: return JValueType.Numeric;
                case SqlDbType.Binary: return JValueType.String;
                case SqlDbType.Bit: return JValueType.String;
                case SqlDbType.Char: return JValueType.String;
                case SqlDbType.DateTime: return JValueType.DateTime;
                case SqlDbType.Decimal: return JValueType.Numeric;
                case SqlDbType.Float: return JValueType.Numeric;
                case SqlDbType.Image: return JValueType.String;
                case SqlDbType.Int: return JValueType.Numeric;
                case SqlDbType.Money: return JValueType.Numeric;
                case SqlDbType.NChar: return JValueType.String;
                case SqlDbType.NText: return JValueType.String;
                case SqlDbType.NVarChar: return JValueType.String;
                case SqlDbType.Real: return JValueType.String;
                case SqlDbType.UniqueIdentifier: return JValueType.String;
                case SqlDbType.SmallDateTime: return JValueType.DateTime;
                case SqlDbType.SmallInt: return JValueType.Numeric;
                case SqlDbType.SmallMoney: return JValueType.Numeric;
                case SqlDbType.Text: return JValueType.String;
                case SqlDbType.Timestamp: return JValueType.DateTime;
                case SqlDbType.TinyInt: return JValueType.Numeric;
                case SqlDbType.VarBinary: return JValueType.String;
                case SqlDbType.VarChar: return JValueType.String;
                case SqlDbType.Variant: return JValueType.String;
                case SqlDbType.Xml: return JValueType.String;
                case SqlDbType.Udt: return JValueType.String;
                case SqlDbType.Structured: return JValueType.String;
                case SqlDbType.Date: return JValueType.DateTime;
                case SqlDbType.Time: return JValueType.DateTime;
                case SqlDbType.DateTime2: return JValueType.DateTime;
                case SqlDbType.DateTimeOffset: return JValueType.DateTime;
            }
            throw new NotSupportedException();
        }
        public int Order { get; set; }

    }

    public class JSource
    {
        public JSource()
        {
            Sources = new List<object>();
            RandOtherFiledCalculateValue = false;
            FromTableCalculateValueUseOtherField = false;
        }
        public JSourceValueCategroy SourceValueCategroy { get; set; }
        public List<object> Sources { get; set; }
        public object MinValue { get; set; }
        public object MaxValue { get; set; }
        public object Step { get; set; }
        public string ReferenceTableName { get; set; }
        public string ReferenceColumnName { get; set; }
        public string RefFilter { get; set; }

        public string OtherFiledName { get; set; }
        public string OtherFiledCalculate { get; set; }
        public int OtherFiledCalculateValue { get; set; }
        public bool RandOtherFiledCalculateValue { get; set; }
        public bool FromTableCalculateValueUseOtherField { get; set; }

    }

    public enum JSourceValueCategroy
    {
        Range,
        List,
        Sequence,
        FromTable,
        OtherField
    }

    public enum JValueType
    {
        Numeric,
        String,
        DateTime,
    }


}
